//Documentation complete
/**
 * @author Jason Funk
 * @version 2009FE18
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <code>Broker</code> for <code>FoodItemBean</code>.
 */
public class FoodItemBroker extends Broker<FoodItemBean> {
	
	/* Singleton Constructor */
	
	private static FoodItemBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private FoodItemBroker () throws ErrorBean {
		insertSQL =
		updateSQL =
		deleteFoodItemSQL = null;
	}
	
	/**
	 * Retrieves the static FoodItemBroker instance.
	 * @return static FoodItemBroker
	 * @throws ErrorBean
	 */
	public synchronized static FoodItemBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new FoodItemBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement insertSQL;
	private PreparedStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "INSERT INTO food_item SET name = ?, description = ?, cost = ?, fk_food_item_type_id = ?, available = ?, active = TRUE");
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE food_item SET name = ?, description = ?, cost = ?, fk_food_item_type_id = ?, available = ?, active = TRUE WHERE food_item_id = ?");
	}
	
	private PreparedStatement deleteFoodItemSQL;
	private PreparedStatement getDeleteItemSQL () throws ErrorBean {
		return prepare(deleteFoodItemSQL, "CALL sp_delete_food_item(?)");
	}
	
	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public synchronized boolean persist (FoodItemBean fib) throws ErrorBean {		
		fib.isValid();
		
		int item_id = fib.getId();
		String name = fib.getName();
		String description = fib.getDescription();
		double cost = fib.getCost();
		System.out.println(fib);
		String type = fib.getType();
		Boolean available = fib.getAvailability();
		
		if(!fib.isValid()) {
			
		}
		else if(!isExistingFoodItemType(fib.getType())) {
			FoodItemTypeBean newBean = new FoodItemTypeBean();
			newBean.setName(fib.getType());
			
			FoodItemTypeBroker fitb = FoodItemTypeBroker.getBroker();
			fitb.persist(newBean);			
		}
		
		int type_id = getTypeID(type);
		
		if (item_id == 0) {
			try {
				PreparedStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, name);
				insertSQL.setString(2, description);
				insertSQL.setDouble(3, cost);
				insertSQL.setInt(4, type_id);
				insertSQL.setBoolean(5, available);
				insertSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (item_id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setString(1, name);
				updateSQL.setString(2, description);
				updateSQL.setDouble(3, cost);
				updateSQL.setInt(4, type_id);
				updateSQL.setBoolean(5, available);
				updateSQL.setInt(6, item_id);
				updateSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public synchronized boolean remove (FoodItemBean fib) throws ErrorBean	{
		int food_item_id = fib.getId();
		
		try {
			PreparedStatement deleteItemSQL = getDeleteItemSQL();
			deleteItemSQL.setInt(1, food_item_id);
			deleteItemSQL.execute();
			
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}
	
	@Override
	public synchronized List<FoodItemBean> search (FoodItemBean searchBean) throws ErrorBean {
		List<FoodItemBean> search = new ArrayList<FoodItemBean>();
		FoodItemBean fib;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM food_item WHERE active = TRUE");
		
		//Search by Item id
		int id = searchBean.getId();
		if(id > 0) {
			sql.append(" AND food_item_id = "+id);
		}
		else {
			//Search by Item name
			String name = searchBean.getName();
			if (name != null && !"".equals(name)) {
				sql.append(" AND name LIKE '%" + name + "%'");
			}
			
			//Search by Item type
			String type = searchBean.getType();
			if (type != null && !"".equals(type)) {
				int type_id = getTypeID(type);
				sql.append(" AND fk_food_item_type_id = " + type_id);
			}
			
			//Search by Item availability
			Boolean available = searchBean.getAvailability();
			if (available != null) {
				sql.append(" AND available = " + available.toString().toUpperCase());
			}
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					fib = new FoodItemBean();
					fib.setId(results.getInt("food_item_id"));
					fib.setName(results.getString("name"));
					fib.setCost(results.getDouble("cost"));
					fib.setDescription(results.getString("description"));
					
					int type_id = results.getInt("fk_food_item_type_id");
					fib.setType(getTypeName(type_id));
					fib.setAvailability(results.getBoolean("available"));
					
					search.add(fib);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	@Override
	public synchronized List<FoodItemBean> listAll() throws ErrorBean {
		List<FoodItemBean> search = new ArrayList<FoodItemBean>();
		FoodItemBean fib;
		
		StringBuilder sql = new StringBuilder("SELECT * FROM food_item WHERE active = TRUE;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					fib = new FoodItemBean();
					fib.setId(results.getInt("food_item_id"));
					fib.setName(results.getString("name"));
					fib.setCost(results.getDouble("cost"));
					fib.setDescription(results.getString("description"));
					
					int type_id = results.getInt("fk_food_item_type_id");
					fib.setType(getTypeName(type_id));
					fib.setAvailability(results.getBoolean("available"));
					
					search.add(fib);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Local Methods */
	
	/**
	 * Takes an Item Type id and returns the Item Type name.
	 * @param typeId the Item Type id
	 * @return name of the Item Type
	 * @throws ErrorBean
	 */
	private String getTypeName (int typeId) throws ErrorBean {
		FoodItemTypeBroker fitb = FoodItemTypeBroker.getBroker();
		List<FoodItemTypeBean> search = new ArrayList<FoodItemTypeBean>();
		
		FoodItemTypeBean toSearch = new FoodItemTypeBean(typeId);
		search = fitb.search(toSearch);
		if (search.size() == 0)
			throwError(
				ITEMTYPE_ID_INVALID,
				String.valueOf(typeId),
				this);
		
		return search.get(0).getName();
	}
	
	/**
	 * Takes an <code>ItemType</code> name and returns the id.
	 * @param typeName the <code>ItemType</code> name
	 * @return id of the <code>ItemType</code>
	 * @throws ErrorBean if the 
	 */
	private int getTypeID (String typeName) throws ErrorBean {
		FoodItemTypeBroker fitb = FoodItemTypeBroker.getBroker();
		List<FoodItemTypeBean> search = new ArrayList<FoodItemTypeBean>();
		
		FoodItemTypeBean toSearch = new FoodItemTypeBean(typeName);
		search = fitb.search(toSearch);
		if(search.size() == 0)
			throwError(
				ITEMTYPE_NAME_INVALID,
				typeName,
				this);
		
		return search.get(0).getId();
	}
	
	/**
	 * Checks if a Food Item Type is valid.
	 * @param type the Food Item Type to validate
	 * @return true if the Food Item Type is valid, false if it is not
	 * @throws ErrorBean
	 */
	private boolean isExistingFoodItemType (String foodItemType) throws ErrorBean {
		FoodItemTypeBean searchBean = new FoodItemTypeBean();
		searchBean.setName(foodItemType);
		
		FoodItemTypeBroker fitb = FoodItemTypeBroker.getBroker();
		List<FoodItemTypeBean> searchItemType = fitb.search(searchBean);
		return !searchItemType.isEmpty();
	}
	
	/* END Local Methods */
	
}
